from typing import Union
from django.db.models import Max
import pandas
import numpy
from django.db.models import Q
from rest_framework import status
from rest_framework.response import Response
from rest_framework.viewsets import ModelViewSet, ViewSet
from rest_framework.permissions import IsAuthenticated
from rest_framework_simplejwt.authentication import JWTAuthentication
from rest_framework.decorators import action
from django.http import HttpResponse,StreamingHttpResponse, FileResponse
from django.core.cache import cache
from django.db.models import Prefetch

import logging
from django.utils import timezone
import csv
import io
import datetime
from openpyxl import Workbook
from openpyxl.styles import PatternFill, Alignment, Font

from tsl_backend.frontend_api.models.route import Difficulty, StoneMountSetting, Master, RouteFlows, OutstandingMaster
from tsl_backend.users.models import Flow
import tsl_backend.frontend_api.models.workorder as workorders
import tsl_backend.frontend_api.serializers.uploadrt_serializer as uploadrt_serializer
import tsl_backend.frontend_api.serializers.serializers as frontend_serializers
import tsl_backend.frontend_api.models.route as routes
import tsl_backend.users.models as users
import tsl_backend.common as common
from tsl_backend.frontend_api.middleware import response as frontend_response
import tsl_backend.frontend_api.utils.pagination as table_pagination
from django.http import HttpRequest
from django.http import QueryDict

def convert_data_to_post(request):
    query_dict = QueryDict(mutable=True)
    query_dict.update(request.data)

    new_request = HttpRequest()
    new_request.method = 'GET'
    new_request.POST = query_dict

    # Copy other relevant attributes from the original request if needed
    new_request.path = request.path
    new_request.META = request.META  # Copy headers and metadata

    # Copy query parameters from the original request
    new_request.GET = query_dict
    new_request.query_params = query_dict

    return new_request

def convert_data_to_get(request):
    query_dict = QueryDict(mutable=True)
    query_dict.update(request.GET)

    new_request = HttpRequest()
    new_request.method = 'GET'
    new_request.POST = query_dict

    # Copy other relevant attributes from the original request if needed
    new_request.path = request.path
    new_request.META = request.META  # Copy headers and metadata

    # Copy query parameters from the original request
    new_request.GET = query_dict
    new_request.query_params = query_dict

    return new_request


def handle_outstanding_order(master_obj):
    
    fg_article = master_obj.fg_article
    design = master_obj.design
    mould = master_obj.mould
    gold = master_obj.gold_type
    orders = []
    # Search order with fg_article
    query = Q()

    query |= Q(fg_article=fg_article)
    query |= Q(design=design, mould=mould, gold_type=gold)
    fg_out_objs = OutstandingMaster.objects.filter(query)
    if fg_out_objs.exists(): 
        # create order route for the outstanding order
        orders+=[fg_out_obj.order for fg_out_obj in fg_out_objs]
    else:
        logging.info(f"No orders with related fg_article: {fg_article}")
        return
    
    logging.info(f"Outstanding orders: {orders}")
    detail_objs=workorders.Detail.objects.filter(order__in=orders)
    for detail_obj in detail_objs:
        logging.info(f"Create order routing for {detail_obj}")
        common.create_order_routing(detail_obj)
    fg_out_objs.delete()

class UploadRouteTableView(ViewSet):

    serializer_class = uploadrt_serializer.UploadRoutingTableSerializer

    # http_method_names = ["get", "post"]
    permission_classes = [IsAuthenticated]
    authentication_classes = [JWTAuthentication]
    pagination_class = table_pagination.ResultsSetPagination

    @action(detail=False, methods=['post'], url_path='importExcel')
    def importExcel(self, request):
        try:
            if request.data["file"] is None: raise MissingParamException
        except MissingParamException:
            return Response(
                {"detail": "Missing Parameter(s)"},
                status=status.HTTP_400_BAD_REQUEST
            )
        else:
            # Get excel file
            file = request.data["file"]

            if file.size > 3145728:
                return Response({"message": "maximum file size 3MB"}, status=status.HTTP_400_BAD_REQUEST)
            try:
                # df = pandas.read_excel(file.read(), sheet_name="Master-1")
                df = pandas.read_excel(file.read(), sheet_name="Master-1a", skiprows=[0, 1, 2])

            except Exception as e:
                logging.warn(f"sheet_name: {e}")
                return Response(
                    {
                       "message": '找不到工作紙 Master-1a'
                    },
                    status=status.HTTP_400_BAD_REQUEST
                )

            if (df.shape[0]+4) > 10510:
                return Response({"message": "限制1500组数据"}, status=status.HTTP_400_BAD_REQUEST)
        
            df = df.fillna(numpy.nan).replace([numpy.nan], [None])

            fgArticles = []
            update_date = []
            create_date = []

            try:
      
                start_value = df.iloc[0].index.get_loc("Step01")

            except Exception as e:
                logging.warn(f"Finding Step01: {e}")
                return Response(
                    {
                        "message": f"列表标题需要由第4行开始"
                    },
                    status=status.HTTP_400_BAD_REQUEST
                )
            
            if not start_value:
                start_value = 8
            
            #  error list
            errorList = []
            total_count=0

            ## TODO VERSION 2 blue update
            update_list_route_flows_list = [] 
            create_list_route_flows_list = []
            update_list_route_flows_list_1 = []
            for chunk in numpy.split(df, df.shape[0]//7):

                loc = [] # error locations
                try:
                    row = chunk.iloc[0,start_value:start_value+20].tolist()
                    num_values = sum(1 for value in row if value is not None)
                    # main data 
                    serial = chunk["序号"].iloc[[0]].values.item()
                    design = str(chunk["设计编号"].iloc[[0]].values.item()).split('.')[0]
                    if design == 'None':
                        design = ''
                    mould = chunk["模号"].iloc[[0]].values.item()
                    if mould == 0:
                        return Response({"message": "模号不能是0"}, status=status.HTTP_400_BAD_REQUEST)
                    gold = chunk["金料成色"].iloc[[0]].values.item()
                    article = chunk["Article"].iloc[[0]].values.item()
                    is_one_inlay = chunk["是否1种镶工"].iloc[[0]].values.item() if chunk["是否1种镶工"].iloc[[0]].values.item() is not None else '否'
                except Exception as e:
                    logging.warn(f"Finding form col field: {e}")
                    return Response(
                        {
                            "message": f"列表标题找不到 {e}"
                        },
                        status=status.HTTP_400_BAD_REQUEST
                    )

                try:
                    mould = int(mould)
                except Exception as e:
                    mould=None
                    logging.warn(f"Fail to convert mould to integer: {mould}")
                temp = {
                    "master_id": 0,
                    "serial": serial,
                    "design": str(design),
                    "mould": mould,
                    "gold": gold,
                    "article": article,
                    "is_one_inlay": is_one_inlay,
                    "item": None
                }
                logging.info(f"Master data: {temp}")
                # store to db [MASTER]
                single_job_value = True if is_one_inlay == "是" else False


                # master_obj, created_master = Master.objects.update_or_create(serial=serial, 
                #                                                              defaults={
                #                                                                  "design":design,
                #                                                                  "mould": mould,
                #                                                                  "gold_type": gold,
                #                                                                  "single_job": single_job_value,
                #                                                                  "fg_article": article
                #                                                                  })

                try:
                    # get fg_article to update master by article because the oustanding master do not have serial 
                    master_obj, created_master = Master.objects.update_or_create(fg_article=article,
                                                                                defaults={
                                                                                    "serial":serial,
                                                                                    "design":design,
                                                                                    "mould": mould,
                                                                                    "gold_type": gold,
                                                                                    "single_job": single_job_value
                                                                                    #  "fg_article": article
                                                                                    })
                    temp['master_id'] = master_obj.id

                
                except Exception as e:
                    logging.error(f"Fail to update Master article [{article}]: {e}")
                else:
                    # Get article id [MASTER.id]
                    master_id = master_obj.id
                    print("serial", serial," master_id: ", master_id)
                    logging.info(f"created_master  Master article [{article}]: {created_master}")

                    # Search In Outstanding Master
                    queryset = OutstandingMaster.objects.all()
                    total_count=queryset.count()
                
                    stepNo = 0
                    item_list = []
                    end_value = start_value + num_values

                    if created_master:
                        logging.info(f"'A new master is created  Master article [{article}]: {created_master}")

                        # new_rf = RouteFlows()
                        # new_rf.flow = flow_obj
                        # new_rf.difficulty = difficulty_obj
                        # new_rf.quantity = qty
                        # new_rf.primary_stone_mount_setting = psms_obj
                        # new_rf.secondary_stone_mount_setting = ssms_obj
                        # new_rf.consumption_percentage = conpercent
                        # new_rf.worker_fee = workerFee
                        # new_rf.master = master_obj
                        # new_rf.save()
                    else: #  Remove all first for updating new # If exist 
                        logging.info(f"'A new master is not created  Master article [{article}]: {created_master}")

                        rfobj = RouteFlows.objects.filter(master__id=master_id)
                        rfobj_cnt = rfobj.count()
                        # if rfobj_cnt > num_values:
                        ## direct delete all ori route
                        del_objs = rfobj.delete()
                            # del_cnt = rfobj_cnt - num_values
                            # del_objs.delete()

                        # num_deleted, delete_info = RouteFlows.objects.filter(master__serial=serial).delete()

                    for index in range(start_value, end_value):
                        stepNo += 1

                        sn = "步骤 "+ str(stepNo)
                        rn = chunk.iloc[0,index]
                        q = chunk.iloc[1,index] # need handle
                        d = chunk.iloc[2,index]
                        psn = chunk.iloc[3,index]
                        ssn = chunk.iloc[4,index]
                        cp = chunk.iloc[5,index] # need handle
                        wf = chunk.iloc[6,index] # need handle


                        item_temp = {
                            "stepNo": sn,
                            "routeName":  rn,
                            "qty" : q,
                            "difficulty": d,
                            "psName": psn,
                            "ssName" : ssn,
                            "consumePercent": cp,
                            "workfee": wf 
                        }

                        err_fields = []

                        # Route Name (Chinese)
                        if rn:
                            flow_obj = Flow.objects.filter(chinese_name=rn.strip()).first()
                            if flow_obj is None: 
                                # err_fields.append({"subject": '生产步骤', "value":rn})
                                err_fields.append(f"<生产步骤>: {rn}")
                                item_temp["stepNo"] = None
                        else:
                            flow_obj = None

                        # Quantity
                        if q:
                            qty = q if is_integer(q) else None
                            if qty is None: 
                                # err_fields.append({"subject": '分件数量', "value":q})
                                err_fields.append(f"<分件数量>: {q}")
                                item_temp["qty"] = None
                        else:
                            qty = None

                        # Difficulty
                        if d:
                            difficulty_obj = Difficulty.objects.filter(name=d.strip()).first()
                            if difficulty_obj is None: 
                                # err_fields.append({"subject": '难易程度', "value":d})
                                err_fields.append(f"<难易程度>: {d}")
                                item_temp["difficulty"] = None
                        else:
                            difficulty_obj = None

                        #  Primary Stone Mount Setting
                        if psn:
                            psms_obj = StoneMountSetting.objects.filter(name=psn.strip()).first()
                            if psms_obj is None: 
                                # err_fields.append({"subject": "镶法技术 (主)", "value":psn})
                                err_fields.append(f"<镶法技术 (主)>: {psn}")
                                item_temp["psName"] = None
                        else:
                            psms_obj = None

                        #  Secondary Stone Mount Setting
                        if ssn:
                            ssms_obj = StoneMountSetting.objects.filter(name=ssn.strip()).first()
                            if ssms_obj is None: 
                                # err_fields.append({"subject": "镶法技术 (次)", "value":ssn})
                                err_fields.append(f"<镶法技术 (次)>: {ssn}") 
                                item_temp["ssName"] = None                      
                        else:
                            ssms_obj = None

                        # Consumption Percentage
                        if cp:
                            conpercent = None

                            if is_float(cp):
                                conpercent = cp

                            if is_integer(cp):
                                conpercent = cp


                            if conpercent is None: 
                                # err_fields.append({"subject": '给耗 (%)', "value":cp})
                                err_fields.append(f"<给耗 (%)>: {cp}") 
                                item_temp["consumePercent"] = None  
                        else:
                            conpercent = None

                        # Worker Fee
                        if wf:
                            workerFee = None
                            if is_float(wf):
                                workerFee = wf

                            if is_integer(wf):
                                workerFee = wf
                      
                            if workerFee is None: 
                                # err_fields.append({"subject": '工人工费 (元)', "value":wf})
                                err_fields.append(f"<工人工费 (元)>: {wf}")
                                item_temp["workfee"] = None  
                        else:
                            workerFee = None

                        # Create new route flow obj
                        
                        routeFlows = RouteFlows.objects.filter(seq=stepNo, master=master_obj)

                        if routeFlows.exists():
                            
                            ## TODO VERSION 2
                            r_f = routeFlows.first()
                            r_f.flow=flow_obj
                            r_f.difficulty=difficulty_obj
                            r_f.quantity=qty
                            r_f.primary_stone_mount_setting=psms_obj
                            r_f.secondary_stone_mount_setting=ssms_obj
                            r_f.consumption_percentage=conpercent
                            r_f.worker_fee=workerFee
                            update_list_route_flows_list.append(r_f)

                            if len(update_list_route_flows_list) >= 300:
                                update_rf_count = RouteFlows.objects.bulk_update(update_list_route_flows_list,["worker_fee","consumption_percentage","secondary_stone_mount_setting","primary_stone_mount_setting","quantity","difficulty","flow"],batch_size = 100)
                                update_list_route_flows_list = []

                            ## VERSION1
                            # routeFlows.update(flow=flow_obj, difficulty=difficulty_obj,
                            #             quantity=qty, primary_stone_mount_setting=psms_obj, secondary_stone_mount_setting=ssms_obj,
                            #             consumption_percentage=conpercent, worker_fee=workerFee,
                            #             )
                        else:
                            ## TODO VERSION 2
                            route_flows = RouteFlows(flow=flow_obj, difficulty=difficulty_obj,
                                                        quantity=qty, primary_stone_mount_setting=psms_obj, secondary_stone_mount_setting=ssms_obj,
                                                        consumption_percentage=conpercent, worker_fee=workerFee,
                                                        seq=stepNo, master=master_obj)
                            create_list_route_flows_list.append(route_flows)
                            
                            if len(create_list_route_flows_list) >= 300:
                                    create_rf_count = RouteFlows.objects.bulk_create(create_list_route_flows_list, batch_size=100)
                                    create_list_route_flows_list = []

                            ## VERSION1
                            # RouteFlows.objects.create(flow=flow_obj, difficulty=difficulty_obj,
                            #                             quantity=qty, primary_stone_mount_setting=psms_obj, secondary_stone_mount_setting=ssms_obj,
                            #                             consumption_percentage=conpercent, worker_fee=workerFee,
                            #                             seq=stepNo, master=master_obj)

                        #  Create or update route flow obj
                        item_list.append(item_temp) # add an article into the list

                        rf_queryset = RouteFlows.objects.filter(master__serial=serial, seq=stepNo)
                        route_flows_updated_values = {
                            "flow":flow_obj,
                            "difficulty": difficulty_obj,
                            "quantity": qty,
                            "primary_stone_mount_setting": psms_obj,
                            "secondary_stone_mount_setting": ssms_obj,
                            "consumption_percentage": conpercent,
                            "worker_fee": workerFee,
                            "master": master_obj
                        }
                        if rf_queryset.exists():
                            try:
                                ## TODO VERSION2
                                rf = rf_queryset.first()
                                for key, value in route_flows_updated_values.items():
                                    setattr(rf, key, value)
                                update_list_route_flows_list_1.append(rf)

                                if len(update_list_route_flows_list_1) >= 300:
                                    RouteFlows.objects.bulk_update(update_list_route_flows_list_1, route_flows_updated_values.keys(), batch_size = 100) 
                                    update_list_route_flows_list_1 = []

                                ## VERSION1
                                # rf_queryset.update(flow=flow_obj, difficulty=difficulty_obj,
                                #             quantity=qty, primary_stone_mount_setting=psms_obj, secondary_stone_mount_setting=ssms_obj,
                                #             consumption_percentage=conpercent, worker_fee=workerFee, master=master_obj
                                #             )
                            except RouteFlows.DoesNotExist:
                                max_id = RouteFlows.objects.aggregate(Max('id'))['id__max']
                                route_flows_updated_values["id"] =  max_id + 1
                                route_flows_updated_values["seq"] = stepNo
                                rf = RouteFlows(**route_flows_updated_values)
                                rf.save()
    
                        if len(err_fields) > 0:
                            loc_item = {
                                "step": sn,
                                "fields": err_fields
                            }
                            loc.append(loc_item)

                    # update outstanding order
                    handle_outstanding_order(master_obj)
                    
                    # update return data
                    temp["item"] = item_list
                    # fgArticles.append(temp)
                    if created_master:
                        create_date.append(temp)
                    else:
                        update_date.append(temp)
                    
                    if len(loc) > 0:
                        err_item = {
                            "serial": serial,
                            "fgArticle": article,
                            "loc": loc
                        }
                        errorList.append(err_item)

            ## TODO VERSION2
            if update_list_route_flows_list:
                RouteFlows.objects.bulk_update(update_list_route_flows_list,["worker_fee","consumption_percentage","secondary_stone_mount_setting","primary_stone_mount_setting","quantity","difficulty","flow"])
            if create_list_route_flows_list:
                RouteFlows.objects.bulk_create(create_list_route_flows_list)
            if update_list_route_flows_list_1:
                RouteFlows.objects.bulk_update(update_list_route_flows_list_1, route_flows_updated_values.keys()) 

            return Response(
                {
                    "info": {
                        "name": file.name,
                        "size": file.size,  # in byte
                        "type": file.content_type,  # determine which engine to use
                    },
                    "create_data": create_date,
                    "update_data": update_date,
                    "errorList": errorList,
                    "outstanding_count": total_count
                },
                status=status.HTTP_200_OK
            )
        
    
    @action(detail=False, methods=['post'], url_path='searchFGArticle')
    def searchFGArticle(self, request):
        # 上传工单流程图搜寻
        try:
            logging.info(f'request to search FGR Article: {request.data}')
            # logging.info(f'request to search FGR Article: {request.POST}')
            if request.data is None: 
                raise MissingParamException
        except MissingParamException:
            logging.error('Missing Parameter')
            return Response(
                {"detail": "Missing Parameter(s)"},
                status=status.HTTP_400_BAD_REQUEST
            )
        else:
            # # Get values
            serial = request.data["serial"]
            fg_article = request.data["fgArticle"]
            design = request.data["design"]
            mould = request.data["mould"]
            gold_type = request.data["goldType"]
            sort_seq=request.data["sort_seq"]
            page_size=request.data["rows_per_page"]
            page=request.data["page"]
            if not sort_seq:
                sort_seq="fg_article"
            
            logging.info(f"Filter for FG article for {serial=}, {fg_article=}, {design=}, {mould=}, {gold_type=}, {sort_seq=}, {page=}, {page_size=}")

            #  Do filtering
            filter_cond = Q()

            if serial is not None:
                filter_cond &= Q(serial__icontains=serial)
            
            if fg_article is not None:
                filter_cond &= Q(fg_article__icontains=fg_article)
            
            if design is not None:
                filter_cond &= Q(design__icontains=design)

            if mould is not None:
                filter_cond &= Q(mould=mould)

            if gold_type is not None:
                filter_cond &= Q(gold_type__icontains=gold_type)

            if serial is None and fg_article is None and design is None and mould is None and gold_type is None:
                master_objs = Master.objects.all()
            else:
                # filter_res = Master.objects.filter(filter_cond).values()
                master_objs = Master.objects.filter(filter_cond)

            paginated_data = []
            if master_objs is not None:
                sort_master_objs = table_pagination.master_article_sorting(master_objs, sort_seq)
                paginator = table_pagination.ResultsSetPagination()
                paginator.page_size = page_size
                new_request = convert_data_to_post(request)
                logging.info(f'----------- searchFGArticle:{new_request.POST}')
                page_master_objs = paginator.paginate_queryset(sort_master_objs, new_request)
                logging.info(f"No. of record for page {paginator.page}: {len(page_master_objs)}")

                for master in page_master_objs:
                    temp = {
                        "master_id": master.id,
                        "serial": master.serial,
                        "design": master.design,
                        "mould": master.mould,
                        "gold": master.gold_type,
                        "article": master.fg_article,
                        "is_one_inlay": "是" if master.single_job else "否",
                        "item": None
                    }

                    items = RouteFlows.objects.filter(master=master).select_related("flow", "difficulty", "primary_stone_mount_setting", "secondary_stone_mount_setting", "master").order_by("seq")
                    item_temp = []
                    stepNo = 0
                    if items is not None:
                        for item in items:
                            stepNo += 1
                            
                            _flow = item.flow.chinese_name if item.flow is not None else None
                            _difficulty = item.difficulty.name if item.difficulty is not None else None
                            _psName = item.primary_stone_mount_setting.name if item.primary_stone_mount_setting is not None else None
                            _ssName = item.secondary_stone_mount_setting.name if item.secondary_stone_mount_setting is not None else None

                            tp = {
                                "stepNo": stepNo,
                                "seq": item.seq,
                                "routeName":  _flow,                                
                                "qty" : item.quantity,
                                "difficulty": _difficulty,                                
                                "psName": _psName,
                                "ssName" : _ssName,
                                "consumePercent": item.consumption_percentage,
                                "workfee": item.worker_fee
                            }
                            item_temp.append(tp)
                    temp["item"] = item_temp

                    paginated_data.append(temp)

            paginated_response = paginator.get_paginated_response(paginated_data)
            data = paginated_response.data  
            # data = paginated_data

            flow_objs = users.Flow.objects.all()
            flow_serializer=frontend_serializers.FlowSerializer(instance=flow_objs, many=True)
            flow_serialized_data = flow_serializer.data

            stone_mount_objs = routes.StoneMountSetting.objects.all()
            stone_mount_serializer = frontend_serializers.StoneMountSettingSerializer(instance=stone_mount_objs, many=True)
            stone_mount_data = stone_mount_serializer.data

            difficulty_objs = routes.Difficulty.objects.all()
            difficulty_serializer = frontend_serializers.DifficultySerializer(instance=difficulty_objs, many=True)
            difficulty_data = difficulty_serializer.data

            return_data = {
                   "result": data,
                   "flows": flow_serialized_data,
                   "stone_mount_data": stone_mount_data,
                   "difficulty_data": difficulty_data
                }
            logging.info(f"Data return: {return_data}")

            return Response(
                return_data,
                status=status.HTTP_200_OK
            )
    
    @action(detail=False, methods=['get'], url_path='searchFGArticleExportExcel')
    def searchFGArticleExportExcel(self, request):
        # 上传共单流程图， 导出全部excel
        try:
            logging.info(f'--- searchFGArticleExportExcel: start ---')
            if request.GET is None: 
                raise MissingParamException
        except MissingParamException:
            logging.error('Missing Parameter')
            return Response(
                {"detail": "Missing Parameter(s)"},
                status=status.HTTP_400_BAD_REQUEST
            )
        else:
            # # Get values
            serial = request.GET.get("serial", None)
            fg_article = request.GET.get("fgArticle", None)
            design = request.GET.get("design", None)
            mould = request.GET.get("mould", None)
            gold_type = request.GET.get("goldType", None)
            sort_seq = request.GET.get("sort_seq", None)
            page_size = request.GET.get("rows_per_page", None)
            page = request.GET.get("page", None)
            if not sort_seq:
                sort_seq="fg_article"
            
            logging.info(f"Filter for FG article for {serial=}, {fg_article=}, {design=}, {mould=}, {gold_type=}, {sort_seq=}, {page=}, {page_size=}")
            
            #  Do filtering
            filter_cond = Q()

            if serial is not None:
                filter_cond &= Q(serial__icontains=serial)
            
            if fg_article is not None:
                filter_cond &= Q(fg_article__icontains=fg_article)
            
            if design is not None:
                filter_cond &= Q(design__icontains=design)

            if mould is not None:
                filter_cond &= Q(mould=mould)

            if gold_type is not None:
                filter_cond &= Q(gold_type__icontains=gold_type)

            if serial is None and fg_article is None and design is None and mould is None and gold_type is None:
                master_objs = Master.objects.all().prefetch_related(
                    Prefetch('routeflows_set', queryset=RouteFlows.objects.select_related(
                        'flow', 'difficulty', 'primary_stone_mount_setting', 'secondary_stone_mount_setting'
                    ))
                )
            else:
                master_objs = Master.objects.filter(filter_cond).prefetch_related(
                    Prefetch('routeflows_set', queryset=RouteFlows.objects.select_related(
                        'flow', 'difficulty', 'primary_stone_mount_setting', 'secondary_stone_mount_setting'
                    ))
                )
            

            count = master_objs.count()
            logging.info(f'--- all count:{count}')
            if master_objs is None or count <= 0:
                frontend_response(status.HTTP_400_BAD_REQUEST, "please follow the request format of the corresponding report", None)
            
            pageSize = 10
            pageTotal = count//pageSize + (1 if (count%pageSize) > 0 else 0)
            logging.info(f'--- pageTotal:{pageTotal}')
            # 创建工作薄
            workbook = Workbook()
            workbook.encoding = 'utf-8'
            # 获取第一个工作表（sheet1）
            sheet = workbook.active  
            # 给工作表1设置标题
            sheet.title = "Master-1a"  

            #  file header
            header = ['序号', '设计编号', '模号', '金料成色', 'Article','是否1种镶工', '项目', '输入内容规则', 
                    'Step01', 'Step02', 'Step03', 'Step04 ', 'Step05','Step06', 'Step07', 'Step08', 'Step09', 'Step10',
                    'Step11', 'Step12', 'Step13', 'Step14', 'Step15', 'Step16', 'Step17', 'Step18', 'Step19', 'Step20',]

            # 从第一行开始写，因为Excel文件的行号是从1开始，列号也是从1开始
            dataRowNum = 1
            # 标题栏的填充
            fill = PatternFill('solid', fgColor='d9d9d9')
            # 标题栏对齐方式： 水平方向居中对齐，垂直方向鼎部对齐
            alignment = Alignment(horizontal="center", vertical="top", wrapText=False, shrinkToFit=False)
            # 内容部分对齐方式： 水平方向居中对齐，垂直方向居中对齐
            contentAlignment = Alignment(horizontal="center", vertical="center", wrapText=False, shrinkToFit=False)
            # 标题栏字体：字体微软雅黑、字号8、粗体
            font = Font(name=u'微软雅黑', size=8, bold=True)
            # 内容部分字体：字体微软雅黑、字号8、非粗体
            contentFont = Font(name=u'微软雅黑', size=8, bold=False)
            # 设置标题头行高
            sheet.row_dimensions[4].height = 30
            # 是否1种镶工 是否1种镶工
            sheet.column_dimensions['E'].width = 17
            # Article 列宽度
            sheet.column_dimensions['F'].width = 10
            # 项目 列宽度
            sheet.column_dimensions['G'].width = 10
            # 输入内容规则 列宽度
            sheet.column_dimensions['H'].width = 10
            
            sheet.cell(row = dataRowNum, column = 1).value = timezone.now().strftime('%Y/%m/%d')
            dataRowNum += 1
            sheet.cell(row = dataRowNum, column = 1).value = ''
            dataRowNum += 1
            sheet.cell(row = dataRowNum, column = 1).value = ''
            dataRowNum += 1

            for i in range(1, len(header) + 1):  
                sheet.cell(row = dataRowNum, column = i).value = header[i - 1]
                sheet.cell(row = dataRowNum, column = i).fill = fill
                sheet.cell(row = dataRowNum, column = i).alignment = alignment
                sheet.cell(row = dataRowNum, column = i).font = font

            previous_data_row_num = 4
            sort_master_objs = table_pagination.master_article_sorting(master_objs, sort_seq)
            # 处理每个 master 对象
            logging.info("--- Write Sheet: [start] ---")
            for master in sort_master_objs:
                temp = {
                    "serial": master.serial,
                    "design": master.design,
                    "mould": master.mould,
                    "gold": master.gold_type,
                    "article": master.fg_article,
                    "is_one_inlay": "是" if master.single_job else "否",
                    "item": []
                }

                # 获取所有关联的 RouteFlows 对象
                items = master.routeflows_set.all()

                if items:
                    for stepNo, item in enumerate(items, start=1):
                        _flow = item.flow.chinese_name if item.flow else None
                        _difficulty = item.difficulty.name if item.difficulty else None
                        _ps_name = item.primary_stone_mount_setting.name if item.primary_stone_mount_setting else None
                        _ss_name = item.secondary_stone_mount_setting.name if item.secondary_stone_mount_setting else None

                        temp["item"].append({
                            "stepNo": stepNo,
                            "routeName": _flow,
                            "qty": item.quantity,
                            "difficulty": _difficulty,
                            "psName": _ps_name,
                            "ssName": _ss_name,
                            "consumePercent": "{:.1f}".format(item.consumption_percentage) if item.consumption_percentage is not None else None,
                            "workfee": "{:.1f}".format(item.worker_fee) if item.worker_fee is not None else None
                        })

                # 将处理好的数据写入到工作表中
                lines: list[list[Union[str, int]]] = convertFGArticleToArray(temp) 
                for line_num, line in enumerate(lines, start=1):
                    data_row_num = line_num + previous_data_row_num
                    for col_num, value in enumerate(line, start=1):
                        # 数据为空的格子不用设置值和样式了
                        if value:
                            sheet.cell(row=data_row_num, column=col_num).value = value
                            sheet.cell(row=data_row_num, column=col_num).font = contentFont
                            sheet.cell(row=data_row_num, column=col_num).alignment = contentAlignment
                            # 格式化数字
                            if (line_num == 5 or line_num == 6) and col_num > 8 and value != '':
                                sheet.cell(row=data_row_num, column=col_num).number_format = '0.0'

                previous_data_row_num += len(lines)

            logging.info("--- Write Sheet: [end] ---")
            now = timezone.now().strftime('%Y%m%dT%H%M%S')
            filename = '"fgarticle_{}.xlsx"'.format(now)
            
            return save_http_response(workbook, filename)
            
    @action(detail=False, methods=['post'], url_path='saveChanges')
    def saveChanges(self, request):
        try:
            print('request.data: ', request.data)
            if request.data is None: 
                raise MissingParamException
        except MissingParamException:
            return Response(
                {"detail": "Missing Parameter(s)"},
                status=status.HTTP_400_BAD_REQUEST
            )
        else:
            # Get Data
            master_id = request.data["master_id"]
            singleJob = request.data["single_job"]
            routeFlows = request.data["route_flows"]

            # Modify Master (single_job field)
            try:
                master_obj = Master.objects.get(pk=master_id)
            except Master.DoesNotExist:
                return Response(
                    {"detail": "Object Not Found"},
                    status=status.HTTP_404_NOT_FOUND
                )
            else:
                print("master_obj",master_obj)
                master_obj.single_job = singleJob
                master_obj.save()

            # Update route_flows
            # num_deleted, delete_info = RouteFlows.objects.filter(master__serial=serialNo).delete()
            # print('num_deleted: ',num_deleted)
            # print('delete_info: ',delete_info)

            # 删除全部的flow再创建
            route_flow_delete_msg = RouteFlows.objects.filter(master__id=master_id).delete()

            # rfobj_cnt = rfobj.count()
            # print('rfobj_cnt: ', rfobj_cnt)
            # num_values = len(routeFlows)
            # print('num_values: ', num_values)
            # if rfobj_cnt > num_values:
            #     del_cnt = rfobj_cnt - num_values
            #     print('del count: ', del_cnt)
            #     del_objs = rfobj.filter(id__in=rfobj.order_by('-seq').values('id')[:del_cnt])


            for rf in routeFlows:
                # print("rf.seq: ", rf["seq"])
                flow_obj = Flow.objects.filter(chinese_name=rf["routeName"]).first() if rf["routeName"] is not None else None
                difficulty_obj = Difficulty.objects.filter(name=rf["difficulty"]).first() if rf["difficulty"] is not None else None
                psms_obj = StoneMountSetting.objects.filter(name=rf["psName"]).first()  if rf["psName"] is not None else None
                ssms_obj = StoneMountSetting.objects.filter(name=rf["ssName"]).first()  if rf["ssName"] is not None else None          

                # rflow_obj = RouteFlows.objects.create(flow=flow_obj, difficulty=difficulty_obj,
                #                                             quantity=rf["qty"], primary_stone_mount_setting=psms_obj, secondary_stone_mount_setting=ssms_objm,
                #                                             consumption_percentage=rf["consumePercent"], worker_fee=rf["workfee"],
                #                                             seq=rf["seq"], master=master_obj)

                rflow_obj, created_rflow_obj = RouteFlows.objects.filter(master__id=master_id).update_or_create(seq=rf["seq"], 
                                                                             defaults={
                                                                                 "flow":flow_obj,
                                                                                 "difficulty": difficulty_obj,
                                                                                 "quantity": rf["qty"],
                                                                                 "primary_stone_mount_setting": psms_obj,
                                                                                 "secondary_stone_mount_setting": ssms_obj,
                                                                                 "consumption_percentage": rf["consumePercent"],
                                                                                 "worker_fee": rf["workfee"],
                                                                                 "master": master_obj
                                                                                 })

            return Response(
                {
                   "result": "success"
                },
                status=status.HTTP_200_OK
            )


class OutstandingMasterView(ModelViewSet):

    queryset = OutstandingMaster.objects.all()
    serializer_class = uploadrt_serializer.OutstandingMasterSerializer

    http_method_names = ['get']
    permission_classes = [IsAuthenticated]
    authentication_classes = [JWTAuthentication]

    def list(self, request):
        message="Success"
        data={}
        total_count=self.get_queryset().count()
        data['total_count']=total_count
        logging.info(f"Total outstanding master: {total_count}")
        
        stone_mount_serialzer=frontend_serializers.StoneMountSettingSerializer(routes.StoneMountSetting.objects.all(), many=True)
        flow_serialzer=frontend_serializers.FlowSerializer(users.Flow.objects.all(), many=True)
        difficulty_serialzer=frontend_serializers.DifficultySerializer(routes.Difficulty.objects.all(), many=True)
        data["routing_table"]={"options":{"stone_mount_list":stone_mount_serialzer.data, "flow": flow_serialzer.data, "difficulty":difficulty_serialzer.data}}
        logging.info(f"return data: {data}")

        return frontend_response(status.HTTP_200_OK, message, data)


    @action(detail=False, methods=['get'])
    def export_csv(self, request):
        now = timezone.now().strftime('%Y%m%dT%H%M%S')

        response = HttpResponse(content_type='text/csv')
        response['Content-Disposition'] = 'attachment; filename="outstanding_master_{}.csv"'.format(now)

        # Define the fields to include in the CSV file
        fields = ['order', 'design', 'mould', 'gold_type', 'fg_article']

        # Create a CSV writer object and write the header row
        writer = csv.writer(response)
        writer.writerow(fields)
        logging.info(f"self.queryset: {self.queryset}")
        # Write the data rows
        for obj in self.get_queryset():
            row = [getattr(obj, field) for field in fields]
            writer.writerow(row)

        return response


class MissingParamException(Exception):
    """Missing Required Parameter!"""
    pass

def convertFGArticleToArray(obj):

    baseField = ['serial', 'design', 'mould', 'gold', 'article', 'is_one_inlay']
    flowField = ['stepNo', 'routeName', 'qty', 'difficulty', 'psName', 'ssName', 'consumePercent', 'workfee']
    lines = []
    base = [nested_dictitem(obj, field) for field in baseField]

    itemheader  = [ '生产步骤', '分件数量 (出)', '难易程度', '镶法技术 (主)', '镶法技术 (次)', '给耗 (%)', '工人工费 (元)',]
    itemheader2 = ['主数据限制', '自行输入', '主数据限制', '主数据限制', '主数据限制', '自行输入', '自行输入',]
    itemheader3 = [1, 2, 3, 4, 5, 6, 7]

    flowItems = obj.get('item')
    if flowItems is not None and len(flowItems) > 0:
        flowRows = []
        for item in flowItems:
            flowRows.append([nested_dictitem(item, field) for field in flowField])
        
        for index in range(0, len(itemheader)):
            line: list[Union[str, int]] = []
            for bitem in base:
                if index == 0:
                    line.append(bitem)
                else:
                    line.append('')
            line.append(itemheader[index])
            line.append(itemheader2[index])
            posVal = itemheader3[index]

            for fitem in flowRows:
                line.append(fitem[int(posVal)])
                
            lines.append(line)
    else:
        for index in range(0, len(itemheader)):
            line: list[Union[str, int]] = []
            for bitem in base:
                if index == 0:
                    line.append(bitem)
                else:
                    line.append('')
            line.append(itemheader[index])
            line.append(itemheader2[index])
            for i in range(0, 8): 
                line.append('')
            lines.append(line)
    return lines

def nested_dictitem(obj, attribute, split_rule='__'):
    """
    This function is responsible for getting the nested record from the given obj parameter
    :param obj: whole item without splitting
    :param attribute: field after splitting
    :param split_rule:
    :return:
    """
    split_attr = attribute.split(split_rule)
    level = len(split_attr)
    if obj:
        objVal = obj.get(split_attr[0])
        if not objVal is None:
            if level == 1:
                if is_date(objVal):
                    return objVal.strftime('%Y-%m-%d')
                return objVal
            elif level == 2:
                if len(objVal) > 0:
                    objVal2 = objVal[0].get(split_attr[1]) 
                    if not objVal2 is None:
                        if is_date(objVal2):
                            return objVal2.strftime('%Y-%m-%d')
                        return objVal2
            else:
                logging.error(f'nested_dictitem not sport level:{level}') 

    return ''

def is_date(date):
    return isinstance(date, datetime.date)


def is_integer(string):
    try:
        int(string)
        return True
    except ValueError:
        return False

def is_float(string):
    try:
        float(string)
        return True
    except ValueError:
        return False



def get_master_data():
    # 首先尝试从缓存中获取数据
    key = "master_all_data"
    cached_data = cache.get(key)
    if cached_data:
        return cached_data
    
    # 如果缓存中不存在数据，则从数据库中查询数据
    master_objs = Master.objects.all().prefetch_related(
        Prefetch('routeflows_set', queryset=RouteFlows.objects.select_related(
            'flow', 'difficulty', 'primary_stone_mount_setting', 'secondary_stone_mount_setting'
        ))
    )
    
    # 将查询结果存入缓存
    cache.set(key, master_objs, timeout=3600)  # 设置缓存过期时间为 1 小时
    
    return master_objs



def save_http_response(workbook, filename):
    output = io.BytesIO()
    workbook.save(output)
    response = HttpResponse(content_type='application/vnd.ms-excel')
    response['Content-Disposition'] = 'attachment; filename="{}"'.format(filename)
    response.write(output.getvalue())  # 在设置HttpResponse的类型时，如果给了值，可以不写这句
    logging.info("---searchFGArticleExportExcel end---")
    return response


def save_file_response(workbook, filename):
    file_path = "/home/chf/python_obj/hkpc-tsl-system/tsl_backend/tsl_backend/frontend_api/fgarticle.xlsx"
    workbook.save(file_path)
    response = FileResponse(open(file_path, "rb"), content_type='application/vnd.ms-excel')
    response['Content-Disposition'] = 'attachment; filename="{}"'.format(filename)
    return response


def generate_excel_file(workbook, filename) :
    """
        Return to the generator and send it block by block to the client
    """
    output = io.BytesIO()
    workbook.save(output)
    output.seek(0)
    while True:
        chunk = output.read(8192*3)  # 按需读取数据块
        if not chunk:
            break
        yield chunk

    